package com.example.demo01.Impl.webImpl;

import com.example.demo01.Dao.webDao.NVWeightDao;
import com.example.demo01.Entity.webEntity.NVWeight;
import com.example.demo01.Entity.webEntity.NameValue;

import static com.example.demo01.utils.DateParseUtils.*;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;


@Repository
public class NVWeightDaoImpl implements NVWeightDao {

    //自动装配
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 按照护理等级获取对应的服务次数，并计算权重
     */
    @Override
    public List<NVWeight> getNVWeightListByNurseLevel() {
        //#按照护理等级(服务次数从小到大排列)
        String sql = "SELECT userinfo1.nurselevel as name,COUNT(*) as value FROM serviceorders,userinfo1 WHERE serviceorders.clientusername=userinfo1.phonenum GROUP BY userinfo1.nurselevel ORDER BY value";
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
        return getWeightByNameValueList(nameValueList);
    }


    /**
     * 按照护理等级获取对应的服务次数，并计算权重——格式化后的数据
     */
    @Override
    public List<List> getNVWeightListByNurseLevelFormat() {
        //数据集
        List<NVWeight> nvWeightList = getNVWeightListByNurseLevel();
        //结果集
        List<List> resLists = new ArrayList<>();
        //为结果集添加数据项字段名称
        List<Object> elementProperty = new ArrayList<>();
        elementProperty.add("标准化值");
        elementProperty.add("服务次数");
        elementProperty.add("护理等级");
        resLists.add(elementProperty);
        //数据集解析
        nvWeightList.forEach(new Consumer<NVWeight>() {
            @Override
            public void accept(NVWeight nvWeight) {
                //实例化子元素
                List<Object> element = new ArrayList<>();
                //为子元素添加数据项
                element.add(nvWeight.getWieght());
                element.add(nvWeight.getValue());
                element.add(nvWeight.getName());
                //将子元素添加到结果集中
                resLists.add(element);
            }
        });
        return resLists;
    }

    /**
     * 按照护理等级获取对应的服务次数——时间限定
     *
     * @param start 起始时间
     * @param end   终止时间
     */
    @Override
    public List<NVWeight> getNVWeightByNurseLevelTime(Long start, Long end) {
        //'2018-01-26'   '2018-02-26'
        String sql = "SELECT userinfo1.nurselevel as name,COUNT(*) as value " +
                "FROM serviceorders,userinfo1 " +
                "WHERE serviceorders.orderstate=1 " +
                "AND serviceorders.clientusername=userinfo1.phonenum " +
                "AND serviceorders.ordertime BETWEEN ? AND  ? " +
                "GROUP BY userinfo1.nurselevel ORDER BY value";
        //毫秒数转换为字符串
        String startTime = getDateStrByFormat(millionsToUtilDate(start), "yyyy-MM-dd HH:mm:ss"),
                endTime = getDateStrByFormat(millionsToUtilDate(end), "yyyy-MM-dd HH:mm:ss");
        //设置查询参数,执行SQL语句
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new Object[]{startTime, endTime}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
        return getWeightByNameValueList(nameValueList);
    }

    /**
     * 按照护理等级获取对应的服务次数——时间限定——格式化后的数据
     *
     * @param start 起始时间
     * @param end   终止时间
     */
    @Override
    public List<List> getNVWeightByNurseLevelTimeFormat(Long start, Long end) {
        //数据集
        List<NVWeight> nvWeightList = getNVWeightByNurseLevelTime(start, end);
        return NVWeightListForamt(nvWeightList, "护理等级");
    }


    /**
     * 按照省级划分
     */
    @Override
    public List<NVWeight> getNVWeightByProvinceCode() {
        String sql = "SELECT chinaencode.domain as name,COUNT(*) as value FROM userinfo1,serviceorders,chinaencode WHERE serviceorders.clientusername=userinfo1.phonenum AND LEFT(userinfo1.idnum,2)=LEFT(chinaencode.cvalue,2) AND RIGHT(chinaencode.cvalue,4)='0000' GROUP BY chinaencode.cvalue ORDER BY value";
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
        return getWeightByNameValueList(nameValueList);
    }

    /**
     * 按照省级划分——时间限定
     *
     * @param start 起始时间
     * @param end   终止时间
     */
    @Override
    public List<NVWeight> getNVWeightByProvinceCodeTime(Long start, Long end) {
        String sql = "SELECT chinaencode.domain as name,COUNT(*) as value FROM userinfo1,serviceorders,chinaencode WHERE serviceorders.ordertime BETWEEN ? AND  ? AND serviceorders.clientusername=userinfo1.phonenum AND LEFT(userinfo1.idnum,2)=LEFT(chinaencode.cvalue,2) AND RIGHT(chinaencode.cvalue,4)='0000' GROUP BY chinaencode.cvalue ORDER BY value";
        //毫秒数转换为字符串
        String startTime = getDateStrByFormat(millionsToUtilDate(start), "yyyy-MM-dd HH:mm:ss"),
                endTime = getDateStrByFormat(millionsToUtilDate(end), "yyyy-MM-dd HH:mm:ss");
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new Object[]{startTime, endTime}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
        return getWeightByNameValueList(nameValueList);
    }


    @Override
    public List<List> getNVWeightByProvinceCodeTimeFormat(Long start, Long end) {
        List<NVWeight> nvWeightList = getNVWeightByProvinceCodeTime(start, end);
        return NVWeightListForamt(nvWeightList, "省级区划");
    }

    /**
     * 按照省级划分——格式化的结果
     */
    @Override
    public List<List> getNVWeightByProvinceCodeFormat() {
        List<NVWeight> nvWeightList = getNVWeightByProvinceCode();
        return NVWeightListForamt(nvWeightList, "省级区划");
    }

    /**
     * 按照市区划分
     */
    @Override
    public List<NVWeight> getNVWeightByCityCode() {
        String sql = "SELECT chinaencode.domain as name,COUNT(*) as value FROM userinfo1,serviceorders,chinaencode WHERE serviceorders.clientusername=userinfo1.phonenum AND LEFT(userinfo1.idnum,4)=LEFT(chinaencode.cvalue,4) AND RIGHT(chinaencode.cvalue,2)='00' GROUP BY chinaencode.cvalue ORDER BY value";
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
        return getWeightByNameValueList(nameValueList);
    }

    /**
     * 按照市区划分——时间限定
     *
     * @param start 起始时间
     * @param end   终止时间
     */
    @Override
    public List<NVWeight> getNVWeightByCityCodeTime(Long start, Long end) {
        String sql = "SELECT chinaencode.domain as name,COUNT(*) as value FROM userinfo1,serviceorders,chinaencode WHERE serviceorders.ordertime BETWEEN ? AND  ? AND serviceorders.clientusername=userinfo1.phonenum AND LEFT(userinfo1.idnum,4)=LEFT(chinaencode.cvalue,4) AND RIGHT(chinaencode.cvalue,2)='00' GROUP BY chinaencode.cvalue ORDER BY value";
        //毫秒数转换为字符串
        String startTime = getDateStrByFormat(millionsToUtilDate(start), "yyyy-MM-dd HH:mm:ss"),
                endTime = getDateStrByFormat(millionsToUtilDate(end), "yyyy-MM-dd HH:mm:ss");
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new Object[]{startTime, endTime}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
        return getWeightByNameValueList(nameValueList);
    }

    @Override
    public List<List> getNVWeightByCityCodeTimeFormat(Long start, Long end) {
        List<NVWeight> nvWeightList = getNVWeightByCityCodeTime(start, end);
        return NVWeightListForamt(nvWeightList, "市级区划");
    }

    /**
     * 按照市区划分——格式化的结果
     */
    @Override
    public List<List> getNVWeightByCityCodeFormat() {
        return NVWeightListForamt(getNVWeightByCityCode(), "市级区划");
    }

    /**
     * 按照县乡划分
     */
    @Override
    public List<NVWeight> getNVWeightByCountryCode() {
        String sql = "SELECT chinaencode.domain as name,COUNT(*) as value FROM userinfo1,serviceorders,chinaencode WHERE serviceorders.clientusername=userinfo1.phonenum AND LEFT(userinfo1.idnum,6)=chinaencode.cvalue GROUP BY chinaencode.cvalue ORDER BY value";
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
        return getWeightByNameValueList(nameValueList);
    }

    @Override
    public List<List> getNVWeightByCountryCodeTimeFormat(Long start, Long end) {
        List<NVWeight> nvWeightList = getNVWeightByCountryCodeTime(start, end);
        return NVWeightListForamt(nvWeightList, "县级区划");
    }

    /**
     * 按照县乡划分——时间限定
     *
     * @param start 起始时间
     * @param end   终止时间
     */
    @Override
    public List<NVWeight> getNVWeightByCountryCodeTime(Long start, Long end) {
        String sql = "SELECT chinaencode.domain as name,COUNT(*) as value FROM userinfo1,serviceorders,chinaencode WHERE serviceorders.ordertime BETWEEN ? AND  ? AND serviceorders.clientusername=userinfo1.phonenum AND LEFT(userinfo1.idnum,6)=chinaencode.cvalue GROUP BY chinaencode.cvalue ORDER BY value";
        //毫秒数转换为字符串
        String startTime = getDateStrByFormat(millionsToUtilDate(start), "yyyy-MM-dd HH:mm:ss"),
                endTime = getDateStrByFormat(millionsToUtilDate(end), "yyyy-MM-dd HH:mm:ss");
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new Object[]{startTime, endTime}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
        return getWeightByNameValueList(nameValueList);
    }

    /**
     * 按照县乡划分——格式化的结果
     */
    @Override
    public List<List> getNVWeightByCountryCodeFormat() {
        List<NVWeight> nvWeightList = getNVWeightByCountryCode();
        return NVWeightListForamt(nvWeightList, "县级区划");
    }


    /**
     * 根据idNum，按月份统计报警信息及其报警次数
     *
     * @param idnum 身份证号
     */
    @Override
    public List<NVWeight> getNVWeightByIdNumUnderMonth(String idnum) {
        //测试：'41088219301111650X'
        String sql = "SELECT CONCAT(YEAR(warnrecord.warntime),\"-\",IF(MONTH(warnrecord.warntime)<10,CONCAT(\"0\",MONTH(warnrecord.warntime)),MONTH(warnrecord.warntime))) as name,UNIX_TIMESTAMP(warnrecord.warntime) as value FROM warnrecord,userinfo1\n" +
                "WHERE userinfo1.idnum=? AND userinfo1.idnum=warnrecord.idnumber ORDER BY name";
        List<NameValue> nameValueList = jdbcTemplate.query(sql, new Object[]{idnum}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
        //统计权值-根据月份累计name出现的次数
        return getNVWeightListByMonth(nameValueList);
    }

    public List<NVWeight> getNVWeightListByMonth(List<NameValue> list) {
        List<NVWeight> resList = new ArrayList<>();
        //统计次数
        list.forEach(new Consumer<NameValue>() {
            @Override
            public void accept(NameValue nameValue) {
                NVWeight nvWeight = new NVWeight(nameValue.getName(), nameValue.getValue(), 1f);
                //查找当前nameValue实例是否存在
                if (!isNameValueInstanceExists(resList, nameValue))
                    resList.add(nvWeight);
            }
        });
        return resList;
    }

    /**
     * 判断当前集合中是否存在目标元素
     */
    public boolean isNameValueInstanceExists(List<NVWeight> list, NameValue nameValue) {
        boolean flag = false;
        for (int i=0;i<list.size();i++){
            if (list.get(i).getName().equals(nameValue.getName())){
                //修改权值
                list.get(i).setWieght(list.get(i).getWieght()+1);
                flag = true;
                break;
            }
        }
        return flag;
    }


    /**
     * NVWeightList结果格式化
     */
    public List<List> NVWeightListForamt(List<NVWeight> nvWeightList, String itemName) {
        //结果集
        List<List> resLists = new ArrayList<>();
        //为结果集添加数据项字段名称
        List<Object> elementProperty = new ArrayList<>();
        elementProperty.add("标准化值");
        elementProperty.add("服务次数");
        elementProperty.add(itemName);
        resLists.add(elementProperty);
        //解析结果集
        nvWeightList.forEach(new Consumer<NVWeight>() {
            @Override
            public void accept(NVWeight nvWeight) {
                //实例化子元素
                List<Object> element = new ArrayList<>();
                //为子元素添加数据项
                element.add(nvWeight.getWieght());
                element.add(nvWeight.getValue());
                element.add(nvWeight.getName());
                //将子元素添加到结果集中
                resLists.add(element);
            }
        });
        return resLists;
    }

    /**
     * 根据NameValue计算Value对应的权重值
     *
     * @param list 有序列表：存储NameValue实例的集合(从小到大的顺序)
     * @return 存储NVWeight实例的有序集合
     */
    public List<NVWeight> getWeightByNameValueList(List<NameValue> list) {
        List<NVWeight> nvWeightList = new ArrayList<>();
        if (list == null || list.size() == 0)
            return nvWeightList;
        //执行业务逻辑代码
        Long maxValue = list.get(list.size() - 1).getValue(),
                minValue = list.get(0).getValue();
        //保证最大值>最小值
        if (maxValue == minValue) {
            //如果最大最小值相等,将权重设置为1
            list.forEach(new Consumer<NameValue>() {
                @Override
                public void accept(NameValue nameValue) {
                    nvWeightList.add(new NVWeight(nameValue.getName(), nameValue.getValue(), 100.0f));
                }
            });
        } else {
            //计算Weight权重值，构造NVWeight实例
            list.forEach(new Consumer<NameValue>() {
                @Override
                public void accept(NameValue nameValue) {
                    //weight
                    nvWeightList.add(
                            new NVWeight(
                                    nameValue.getName(),
                                    nameValue.getValue(),
                                    ((float) (nameValue.getValue() - minValue)) * 100 / (maxValue - minValue)));
                }
            });
        }
        return nvWeightList;
    }

}
